package com.textmanager.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseHelper {
    private static final String DB_URL = "jdbc:sqlite:text_manager.db";

    public static Connection connect() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    public static void initializeDatabase() {
        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {

            // Create directories table
            String sqlCreateDirectoriesTable = "CREATE TABLE IF NOT EXISTS directories (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT NOT NULL," +
                    "parent_id INTEGER," +
                    "FOREIGN KEY (parent_id) REFERENCES directories(id) ON DELETE CASCADE" +
                    ");";
            stmt.execute(sqlCreateDirectoriesTable);

            // Create files table
            String sqlCreateFilesTable = "CREATE TABLE IF NOT EXISTS files (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT NOT NULL," +
                    "content TEXT," +
                    "directory_id INTEGER NOT NULL," +
                    "FOREIGN KEY (directory_id) REFERENCES directories(id) ON DELETE CASCADE" +
                    ");";
            stmt.execute(sqlCreateFilesTable);

            // Insert a root directory if it doesn't exist
            String sqlCheckRoot = "SELECT id FROM directories WHERE name = '根目录' AND parent_id IS NULL";
            if (!stmt.executeQuery(sqlCheckRoot).next()) {
                String sqlInsertRoot = "INSERT INTO directories (name) VALUES ('根目录')";
                stmt.execute(sqlInsertRoot);
            }

            System.out.println("Database initialized successfully.");

        } catch (SQLException e) {
            System.err.println("Error initializing database: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // TODO: Add methods for CRUD operations on directories and files
}